import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl import Workbook

# 设置指定列的宽度
def set_column_widths(ws):
    ws.column_dimensions['A'].width = 20
    ws.column_dimensions['B'].width = 12
    ws.column_dimensions['C'].width = 30
    ws.column_dimensions['G'].width = 16


# 读取 Excel 文件
file_path = '.\material\重修课时1.xlsx'
df = pd.read_excel(file_path, header=4)

# 获取唯一的开课学院
unique_departments = df['开课学院'].unique()

# 遍历并保存每个学院的分表
for department in unique_departments:
    # 筛选对应学院的内容
    department_df = df[df['开课学院'] == department]

    # 创建一个新的Excel文件
    department_file_name = f"material\重修课程分院\{department}_重修课时.xlsx"
    with pd.ExcelWriter(department_file_name, engine='openpyxl') as writer:
        # 写入筛选结果到文件
        department_df.to_excel(writer, index=False, sheet_name=department)

        # 获取 OpenPyXL 工作表对象
        wb = writer.book
        ws = wb.active

        # 设置列宽
        set_column_widths(ws)


    print(f"{department_file_name} 文件已创建！")